package com.zhang;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class Excel extends HttpServlet {	
	/**
	 *    查询数据库中的数据，然后将其导出到Excel表中。将导出的Excel表压缩成Zip文件。
	 *    点击导出后可以将Zip文件下载到文本地。单元格可以合并，可以设置背景色和居中
	 */
	private static final String file="D:/ziptest/";
	private static final String zipFile="D:/ziptest/";
	private static final long serialVersionUID = 1L;
	
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		System.out.println(request.getParameter("name"));
		
		response.setHeader("Access-Control-Allow-Origin", "*");
		response.setHeader("Access-Control-Allow-Methods", "*");
		response.setHeader("Access-Control-Allow-Headers", "x-requested-with,content-type");	     
		response.setCharacterEncoding("utf-8");
		//response.setContentType("text/html;charset=utf-8");
		response.setContentType("application/json;charset=utf-8");
        
		//得到数据库连接
		Connection conn=DBUtil.getConnection();
        String sql="SELECT id AS id ,username AS 用户名 ,birthday AS 生日,sex AS 性别,address AS 地址 FROM zip";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
			//设置每次只读取一部分数据
			pstmt.setFetchSize(1000);
			pstmt.setFetchDirection(ResultSet.FETCH_REVERSE);
			ResultSet	rs = pstmt.executeQuery();
			
			//一旦这个Workbook中数据量超过1000就会写入到磁盘中
			SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
			Sheet sheet = (Sheet) workbook.createSheet();
			//设置宽度 sheet.setColumnWidth(m, “列名”.getBytes().length*2*256);
			sheet.setColumnWidth(0, 1000);
			sheet.setColumnWidth(1, 2000);
			sheet.setColumnWidth(2, 3000);
			sheet.setColumnWidth(3, 1000);
			sheet.setColumnWidth(4, 4000);
			workbook.setSheetName(0,  "sheet1");
			Row row = sheet.createRow((short) 0);
			Cell cell;
			CellStyle cellStyle;
			//单元格合并
			sheet.addMergedRegion(new CellRangeAddress( 0,  0,  0,  4));//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
			cell = row.createCell((short) 0);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(new XSSFRichTextString("数据流向"));
			//设置背景色
			cellStyle=workbook.createCellStyle();
			cellStyle.setFillForegroundColor(IndexedColors.CORAL.getIndex());
			cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

			cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
//			cellStyle.setAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
			cell.setCellStyle(cellStyle);

			/*	
			cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
			 cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框 
			cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框 
			cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框 
			 */			

			//获取有多少列
			ResultSetMetaData md = rs.getMetaData();
			int nColumn = md.getColumnCount();
			row =  sheet.createRow(1);
			cellStyle=workbook.createCellStyle();
			for (int i = 1; i <= nColumn; i++) {
				cell = row.createCell((short) (i-1));
				//设置数据类型Cell.CELL_TYPE_STRING Cell.CELL_TYPE_NUMERIC Cell.CELL_TYPE_FORMULA Cell.CELL_TYPE_BOOLEAN Cell.CELL_TYPE_BLANK Cell.CELL_TYPE_ERROR
				cell.setCellType(Cell.CELL_TYPE_STRING);
				//设置值
				cell.setCellValue(new XSSFRichTextString(md.getColumnLabel(i)));
				//设置背景色
				cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
				cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
				cell.setCellStyle(cellStyle);
			}

			int iRow = 2;
			while (rs.next()) {
				row =  sheet.createRow(iRow);
				for (int j = 1; j <= nColumn; j++) {
					cell = row.createCell((short) (j - 1));
					cell.setCellType(Cell.CELL_TYPE_STRING);
					if (rs.getObject(j) != null) {
						cell.setCellValue(new XSSFRichTextString(rs.getObject(j).toString()));
					} else {
						cell.setCellValue("");
					}
				}
				//每1000行将数据存入硬盘
				if (iRow % 1000 == 0) {
					((SXSSFSheet) sheet).flushRows();
				}
				iRow++;
			}

			String filename=System.currentTimeMillis()+".xlsx";
			String savefile=file+filename;
			String zipfilename=System.currentTimeMillis()+".zip";
			String zipsavefile=zipFile+zipfilename;
			FileOutputStream fOut = new FileOutputStream(savefile);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();

			//将文件转换成Zip文件
			FiletoZip(savefile, zipsavefile);
            
			//将下载路径返回给前端
			PrintWriter  out = response.getWriter();  
			out.write("{\"StringUrl\":\""+zipfilename+"\"}");
			out.close();  
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	/**
	 * 将文件压缩成zip文件
	 * @param file 文件路径
	 * @param zipFile 压缩后的文件路径
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private void FiletoZip(String file, String zipFile) throws IOException,FileNotFoundException {
		File f = new File(file);
		File ff = new File(zipFile);
		if (!f.exists()) {
			ff.createNewFile();
		}
		FileInputStream in = new FileInputStream(file);
		FileOutputStream out = new FileOutputStream(zipFile);
		ZipOutputStream zipOut = new ZipOutputStream(out);
		String fileName = file.substring(file.lastIndexOf('/') + 1, file.length());
		// 创建指向压缩原始文件的入流
		ZipEntry entry = new ZipEntry(fileName);
		zipOut.putNextEntry(entry);
		// 向压缩文件中输出数据
		int number = 0;
		byte[] buffer = new byte[512];
		while ((number = in.read(buffer)) != -1) {
			zipOut.write(buffer, 0, number);
		}
		zipOut.close();
		out.close();
		in.close();
		
//		f.delete();
	}

	/**
	 * 设置跨越
	 */
	@Override
	protected void doOptions(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		resp.setHeader("Access-Control-Allow-Credentials", "false");
		resp.setHeader("Access-Control-Allow-Origin", "*");
		resp.setHeader("Access-Control-Allow-Methods", "*");
		resp.setHeader("Access-Control-Allow-Headers", "x-requested-with,content-type");
		resp.setContentType("application/json");
		resp.setCharacterEncoding("utf-8");
		super.doOptions(req, resp);
	}
}
